package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.model.DicArea;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.StdCompinfoLog;
import com.hsnn.medstgmini.base.std.model.form.StdCompInfoForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.BaseDelrelationByCentreDao;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelation;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelationByCentre;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelationLog;
import com.hsnn.medstgmini.pro.base.model.BaseGoods;
import com.hsnn.medstgmini.pro.base.model.BaseGoodsLog;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.PinYin;

@Repository("baseDelrelationByCentreDao")
public class BaseDelrelationByCentreDaoImpl implements
		BaseDelrelationByCentreDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	private Session getSession() {
		Session session = sessionFactory.getCurrentSession();
		if (null == session) {
			session = sessionFactory.openSession();
		}
		return session;
	}

	@Autowired
	private HibernateSqlUtil utilDao;

	@Autowired
	private HibernateSqlUtil dao;

	public void confirmDel(Pagination page) {
		BaseDelrelationByCentre data = (BaseDelrelationByCentre) dao
				.getUniquerResult(BaseDelrelationByCentre.class, page
						.getConditions().get("delRelId"));
		try {
			data.setStatus(Short.valueOf(page.getConditions().get("status")));
			dao.add(data);
			writeLog(data);
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	private void writeLog(BaseDelrelationByCentre data) {
		if (null != data) {
			BaseDelrelationLog log = new BaseDelrelationLog();
			BeanUtils.copyProperties(data, log);
			log.setAreaCode(data.getDicArea().getAreaid());
			log.setProdCompId(data.getStdCompinfo().getCompId());
			log.setDelCompId(data.getStdPSCompinfo().getCompId());
			log.setLogId(UUID.randomUUID().toString());
			dao.add(log);
		}

	}

	/**
	 * 
	 * @category 中心端配送关系管理明细列表
	 * @author 言科
	 * @date 2015年6月26日 Pagination
	 */
	public Pagination getBaseDelByCentreListSee(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT e.del_rel_id,a.goods_code,c.drug_code,c.drug_name,c.form_name,c.spec_name,c.factor,c.unit,c.material_name,c.drug_source,a.drug_cat,d.name")
				.append(" From base_goods a ")
				.append(" left join view_drug c on c.drug_id=a.drug_id ")
				.append(" left join bid_project d on d.proj_id=a.proj_id ")
				.append(" left join base_delrelation e on e.goods_code=a.goods_code where 1=1 ");

		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				sql.append(" and a.goods_code=:goodsCode");
				params.put("goodsCode", map.get("goodsCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugCode"))) {
				sql.append(" and b.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugName"))) {
				sql.append(" and b.drug_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
			if (StringUtils.isNotBlank(map.get("drugSource"))) {
				sql.append(" and b.drug_source=:drugSource");
				params.put("drugSource", map.get("drugSource").trim());
			}
		}
		page = utilDao.getSqlPageList(sql.toString(), params, page);
		return page;
	}

	/**
	 * 
	 * @category 中心端配送关系管理 设置配送关系列表
	 * @author 言科
	 * @date 2015年6月26日 Pagination
	 */
	public Pagination getBaseDelByCentreListManage(Pagination page) {
		StringBuffer sql = new StringBuffer()

				/*
				 * .append(
				 * " select b.comp_id,d.comp_name,a.area_id,a.area_name from dic_area a, bid_projcompany b "
				 * ) .append(" left join bid_project c on c.proj_id=b.proj_id ")
				 * .append(" left join std_compinfo d on d.comp_id=b.comp_id ")
				 * .append(" where a.area_id not like '%00' ")
				 * .append(" and c.`status`=1 ") .append(" and not EXISTS ")
				 * .append(
				 * " ( select * from base_delrelation e where del_comp_id=b.comp_id and e.area_code=a.area_id and e.goods_code='' ) "
				 * ); Map<String, String> map = page.getConditions();
				 */

				.append("SELECT a.comp_id,a.comp_code,a.comp_name,b.ar_fullname,c.comp_reg_name,a.org_code ")
				.append(" from std_compinfo a left join dic_area b on b.area_id=a.area_id left join dic_comptype c on c.comp_reg_id=a.comp_reg_id")
				.append(" where a.`status`=1 and a.comp_type=2");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("compCode"))) {
				sql.append(" and a.comp_code=:compCode");
				params.put("compCode", map.get("compCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and a.comp_name like :compName");
				params.put("compName", "%" + map.get("compName").trim() + "%");
			}
		}
		page = utilDao.getSqlPageList(sql.toString(), params, page);
		return page;
	}

	/**
	 * 
	 * @category 中心端配送关系管理 设置配送关系列表 根据区域设置配送企业
	 * @author 言科
	 * @date 2015年6月26日 Pagination
	 */
	public String setDelByArea(List<StdCompInfoForm> list) {
		if (list != null && list.size() > 0) {
			for (StdCompInfoForm compInfo : list) {
				if (compInfo != null && compInfo.getCompId() != null) {
					StdCompInfoForm data = getCompInfo(compInfo.getCompId());
					data.setUpdDatetime(compInfo.getUpdDatetime());
					try {
						getSession().update(data);
						getSession().flush();
						writeLog(getCompInfo(compInfo.getCompId()));
					} catch (HibernateException e) {
						e.printStackTrace();
						return "failed";
					}
				}
			}
			return "success";
		}
		return null;
	}

	private void writeLog(StdCompInfoForm compInfo) {
		StdCompinfoLog log = new StdCompinfoLog();
		BeanUtils.copyProperties(compInfo, log);
		log.setLogId(UUID.randomUUID().toString());
		getSession().save(log);
		getSession().flush();

	}

	private StdCompInfoForm getCompInfo(String compId) {
		StdCompInfoForm data = (StdCompInfoForm) getSession().get(
				StdCompInfoForm.class, compId);
		return data;
	}

	/**
	 * 
	 * @category 中心端配送关系管理 设置配送关系列表 根据区域设置配送企业 写配送关系日志
	 * @author 言科
	 * @date 2015年6月26日 Pagination
	 */
	private void writeLog(BaseDelrelation data) {
		if (null != data) {
			BaseDelrelationLog log = new BaseDelrelationLog();
			BeanUtils.copyProperties(data, log);
			log.setAreaCode(data.getDicArea().getAreaid());
			log.setProdCompId(data.getStdCompinfo().getCompId());
			log.setDelCompId(data.getStdPSCompinfo().getCompId());
			log.setLogId(UUID.randomUUID().toString());
			utilDao.add(log);
		}
	}

	/**
	 * 
	 * @category 中心端配送关系管理 设置配送关系列表 根据区域设置配送企业 根据商品码，配送企业，配送区域查看有没有记录
	 * @author 言科
	 * @date 2015年6月26日 Pagination
	 */
	private List<Object[]> getBaseDelrelationListBy(String drugId,
			String prodCompId, String areaId) {
		String sql = "SELECT * from base_delrelation a where a.area_code=:areaCode and a.goods_code=:drugId and a.del_comp_id=:compId ";

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("areaCode", areaId);
		params.put("drugId", drugId);
		params.put("compId", prodCompId);
		List<Object[]> list = (List<Object[]>) utilDao.getSqlList(sql, params);
		return list;
	}

	/**
	 * 
	 * @category 查询所有药品和配送企业关系
	 * @author 言科
	 * @date 2015年6月26日 void
	 */
	public Pagination getBaseDelByCompList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT a.del_rel_id,a.goods_code,c.drug_name,c.drug_code,d.comp_name,e.ar_fullname,")
				.append("c.form_name,c.spec_name,c.factor,c.unit,b.drug_cat,a.`status` ")
				.append(" from base_delrelation a left join base_goods b on a.goods_code=b.goods_code")
				.append(" left join view_drug c on c.drug_id=b.drug_id  left join std_compinfo d on d.comp_id=a.del_comp_id")
				.append(" left join dic_area e on e.area_id=a.area_code")
				.append(" where c.prod_comp_id=:compId");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", map.get("compId").trim());
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				sql.append(" and a.goods_code=:goodsCode");
				params.put("goodsCode", map.get("goodsCode").trim());
			}

			if (StringUtils.isNotBlank(map.get("drugCode"))) {
				sql.append(" and c.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("drugName"))) {
				sql.append(" and c.drug_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
			if (StringUtils.isNotBlank(map.get("prodPompId"))) {
				sql.append(" and c.prod_comp_id=:prodPompId");
				params.put("prodPompId", map.get("prodPompId").trim());
			}
			if (StringUtils.isNotBlank(map.get("status"))) {
				sql.append(" and a.`status`=:status");
				params.put("status", map.get("status").trim());
			}
		}
		return utilDao.getSqlPageList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 根据商品编码查询信息
	 * @author 言科
	 * @date 2015年6月24日 BaseDelrelationByCentre
	 */
	public BaseDelrelationByCentre getBaseDelrelationByCentreById(
			String goodsCode) {
		BaseDelrelationByCentre data = (BaseDelrelationByCentre) getSession()
				.get(BaseDelrelationByCentre.class, goodsCode);
		return data;
	}

	/**
	 * 
	 * @category 中心端配送关系 状态变换
	 * @author 言科
	 * @date 2015年6月20日 Pagination
	 */
	public void switchStatus(String goodsCode, Short status) {
		BaseDelrelationByCentre data = getBaseDelrelationByCentreById(goodsCode);
		data.setStatus(status);
		data.setUpdDatetime(new Date());
		getSession().update(data);
		getSession().flush();
		writeLog(data);

	}

	/**
	 * 
	 * @category 配送关系设置日志查询
	 * @author 言科
	 * @date 2015年6月24日 List<BaseDelrelationLog>
	 */
	public List<BaseDelrelationLog> getBaseDelrelationByCentreListLog(
			Pagination page, BaseDelrelationLog baseDelrelationLog) {

		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from base_delrelation_log l where l.del_rel_id = :delRelId ");
		if (baseDelrelationLog != null) {
			Date start = baseDelrelationLog.getStartTime();
			if (start != null) {
				sql.append(" and l.upd_datetime >= :startTime ");
			}
			Date end = baseDelrelationLog.getEndTime();
			if (end != null) {
				sql.append(" and l.upd_datetime <= :endTime ");
			}
		}

		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");

		SQLQuery query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());

		query.setString("delRelId", baseDelrelationLog.getDelRelId());
		countQuery.setString("delRelId", baseDelrelationLog.getDelRelId());
		if (baseDelrelationLog != null) {
			Date start = baseDelrelationLog.getStartTime();
			if (start != null) {
				query.setTimestamp("startTime", start);
				countQuery.setTimestamp("startTime", start);
			}
			Date end = baseDelrelationLog.getEndTime();
			if (end != null) {
				query.setTimestamp("endTime", end);
				countQuery.setTimestamp("endTime", end);
			}
		}
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());

		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.addEntity(BaseDelrelationLog.class);
		return query.list();

	}

	/**
	 * 
	 * @category 中心端配送关系根据商品码查询商品价格明细
	 * @author 言科
	 * @date 2015年6月20日 Pagination
	 */
	public Pagination getBaseDelByCentreGoods(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.del_rel_id,a.goods_code,c.drug_code,c.drug_name,c.form_name,c.spec_name,c.factor,c.unit,b.pro_price_limit,b.retail_price_limit,c.drug_id ")
				.append(" from base_delrelation a ")
				.append(" left join base_goods b on a.goods_code=b.goods_code ")
				.append(" left join view_drug c on c.drug_id=b.drug_id ");

		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {

			if (StringUtils.isNotBlank(map.get("drugCode"))) {
				sql.append(" and c.drug_code=:drugCode");
				params.put("drugCode", map.get("drugCode").trim());
			}

			if (StringUtils.isNotBlank(map.get("drugName"))) {
				sql.append(" and c.drug_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}

			if (StringUtils.isNotBlank(map.get("formName"))) {
				sql.append(" and c.form_name=:formName");
				params.put("formName", map.get("formName").trim());
			}

			if (StringUtils.isNotBlank(map.get("specName"))) {
				sql.append(" and c.spec_name=:specName");
				params.put("specName", map.get("specName").trim());
			}

			if (StringUtils.isNotBlank(map.get("factor"))) {
				sql.append(" and c.factor=:factor");
				params.put("factor", map.get("factor").trim());
			}

			if (StringUtils.isNotBlank(map.get("unit"))) {
				sql.append(" and c.unit=:unit");
				params.put("unit", map.get("unit").trim());
			}

			if (StringUtils.isNotBlank(map.get("proPriceLimit"))) {
				sql.append(" and b.pro_price_limit=:proPriceLimit");
				params.put("proPriceLimit", map.get("proPriceLimit").trim());
			}

			if (StringUtils.isNotBlank(map.get("retailPriceLimit"))) {
				sql.append(" and b.retail_price_limit=:retailPriceLimit");
				params.put("retailPriceLimit", map.get("retailPriceLimit")
						.trim());
			}

		}

		return dao.getSqlPageList(sql.toString(), params, page);

	}

	

}
